unit crud;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, StdCtrls, ZAbstractTable, ZDataset, DB,
  ZAbstractRODataset, ZAbstractDataset, ZConnection, Setting;

type
  TcrudForm = class(TForm)
    GroupBox1: TGroupBox;
    StringGrid1: TStringGrid;
    btnHapus: TButton;
    btnUbah: TButton;
    btnTambah: TButton;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    GroupBox2: TGroupBox;
    btnTutup: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    txtUsername: TEdit;
    ComboBox1: TComboBox;
    txtPass: TEdit;
    btnSimpan: TButton;
    Label4: TLabel;
    ComboBox2: TComboBox;
    Label5: TLabel;
    ComboBox3: TComboBox;
    rbkota: TRadioButton;
    rbkec: TRadioButton;
    procedure StringGrid1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnTambahClick(Sender: TObject);
    procedure btnUbahClick(Sender: TObject);
    procedure btnHapusClick(Sender: TObject);
    procedure btnTutupClick(Sender: TObject);
    procedure btnSimpanClick(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure rbkotaClick(Sender: TObject);
    procedure rbkecClick(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    constructor Create(AOwner: TComponent; username: String; kabkot:String);             overload;
    procedure AutoSizeCol(Grid: TStringGrid; Column: integer);
    function cekRole(nama:String) : Boolean;
    function cekAvail(nama:String) : Boolean;
    procedure refresh;
  end;

var
  crudForm: TcrudForm;
  uname,ukode,ukec,uubah,nukode,skec:string;
  tipe:integer;

implementation

uses DateUtils;

{$R *.dfm}
  constructor TcrudForm.Create(AOwner: TComponent; username: String; kabkot:String);
  begin
    uname:=username;
    //showmessage(inttostr(length(kabkot)));
    //showmessage(kabkot);
    if(length(kabkot)>2)then
    begin
      ukode:=kabkot;
      ukec:=copy(kabkot,3,3);
    end
    else
    begin
      ukode:=kabkot;
      ukec:='';
    end;
    //ukode:=kabkot;
    inherited Create(AOwner);
  end;

  
procedure TcrudForm.StringGrid1Click(Sender: TObject);
begin
  if (StringGrid1.Row>0) and (StringGrid1.Cells[1,1]<>'') then begin
    uubah:=StringGrid1.Cells[1,StringGrid1.row];
    btnHapus.Enabled:=true;
    btnUbah.enabled:=true;
//    ShowMessage(uubah);
  end;
end;

procedure TcrudForm.FormCreate(Sender: TObject);
var
  setting:TSetting_Form;
  crow,i:integer;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  //
  StringGrid1.ColCount:=5;
  StringGrid1.RowCount:=2;
  StringGrid1.Cells[0,0]:='No';
  StringGrid1.Cells[1,0]:='Username';
  StringGrid1.Cells[4,0]:='Role';
  StringGrid1.Cells[2,0]:='Kabupaten/Kota';
  StringGrid1.Cells[3,0]:='Kecamatan';
  StringGrid1.FixedRows:=1;
  ZTable1.Active:=false;
  ZTable1.TableName:='user';
  //showmessage(ukode);
  //showmessage(ukec);
  if(ukec='')then
  begin
    if (ukode<>'00') then begin
      ZTable1.Filter:='KODE_KABKOT='+QuotedStr(ukode);
      ZTable1.Filtered:=true;
    end else
      ZTable1.Filtered:=false;
  end
  else
  begin
    ZTable1.Filter:='KODE_KEC='+QuotedStr(ukec)+' AND KODE_KABKOT='+QuotedStr(copy(ukode,1,2));
    ZTable1.Filtered:=true;
  end;
  ZTable1.Active:=true;
  ZTable1.First;
  if (ZTable1.RecordCount>0) then begin
    for crow:=1 to ZTable1.RecordCount do begin
      StringGrid1.RowCount:=crow+1;
      StringGrid1.Cells[0,crow]:=IntToStr(crow);
      StringGrid1.Cells[1,crow]:=ZTable1.fieldbyname('username').AsString;
      if (ZTable1.FieldByName('ROLE').AsString='1') then
        StringGrid1.Cells[4,crow]:='Admin'
      else
        StringGrid1.Cells[4,crow]:='Petugas';
      with zquery1 do begin
        active:=false;
        sql.Clear;
        sql.Text:='SELECT TAMPIL FROM kabkot WHERE KODE='+QuotedStr(ZTable1.fieldbyname('KODE_KABKOT').AsString);
        Active:=true;
        first;
      end;
      StringGrid1.Cells[2,crow]:=zquery1.Fields[0].AsString;
      if(ZTable1.FieldByName('KODE_KEC').AsString<>'000') then
      begin
        with zquery1 do begin
          active:=false;
          sql.Clear;
          sql.Text:='SELECT TAMPIL FROM kecamatan WHERE KODE_KECAMATAN='+QuotedStr(ZTable1.fieldbyname('KODE_KABKOT').AsString+ZTable1.FieldByName('KODE_KEC').AsString);
          Active:=true;
          first;
        end;
        StringGrid1.Cells[3,crow]:=zquery1.Fields[0].AsString;
      end
      else
      begin
        StringGrid1.Cells[3,crow]:='-';
      end;

      ZTable1.Next;
    end;
    for i := 0 to StringGrid1.ColCount - 1 do
      AutoSizeCol(StringGrid1, i);
  end;
  //
  btnUbah.Enabled:=false;
  btnHapus.Enabled:=false;
  GroupBox2.Visible:=false;
  ComboBox1.Clear;
  ComboBox1.Items.Add('Petugas');
  ComboBox1.Items.Add('Admin');
  ComboBox1.ItemIndex:=0;
  //
  ComboBox2.Clear;
  ComboBox2.items.Add('PILIH KABUPATEN/KOTA');
  ComboBox2.ItemIndex:=0;
  ComboBox3.Clear;
  ComboBox3.items.Add('PILIH KECAMATAN');
  ComboBox3.ItemIndex:=0;
  skec:='';
  if (ukode<>'00') then begin
    rbkota.Enabled:=false;
    rbkota.Checked:=false;
    rbkec.Checked:=true;
    with zquery1 do begin
      active:=false;
      sql.clear;
      sql.Text:='SELECT * FROM kabkot WHERE KODE='+QuotedStr(ukode);
      Active:=true;
      first;
    end;
    ComboBox2.Items.Add(ZQuery1.fieldbyname('TAMPIL').AsString);
    ComboBox2.ItemIndex:=1;
    ComboBox2.Enabled:=false;
    nukode:=ukode;

    with zquery1 do begin
      active:=false;
      sql.clear;
      sql.Text:='SELECT * FROM kecamatan where KODE_KABKOT='+quotedstr(nukode);
      Active:=true;
      first;
    end;
    while (not zquery1.Eof) do begin
      ComboBox3.Items.Add(ZQuery1.fieldbyname('TAMPIL').AsString);
      zquery1.Next;
    end;

  end else begin
    rbkota.Checked:=true;
    ComboBox3.Enabled:=false;
    with zquery1 do begin
      active:=false;
      sql.clear;
      sql.Text:='SELECT * FROM kabkot';
      Active:=true;
      first;
    end;
    while (not zquery1.Eof) do begin
      ComboBox2.Items.Add(ZQuery1.fieldbyname('TAMPIL').AsString);
      zquery1.Next;
    end;
    nukode:='';
  end;
end;

procedure TcrudForm.AutoSizeCol(Grid: TStringGrid; Column: integer);
var
  i, W, WMax: integer;
begin
  WMax := 0;
  for i := 0 to (Grid.RowCount - 1) do begin
    W := Grid.Canvas.TextWidth(Grid.Cells[Column, i]);
    if W > WMax then
      WMax := W;
  end;
  Grid.ColWidths[Column] := WMax + 5;
end;

procedure TcrudForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := cafree;
end;

procedure TcrudForm.btnTambahClick(Sender: TObject);
begin
  if (cekRole(uname)) then begin
    {with ZQuery1 do begin
      active:=false;
      sql.clear;
      sql.text:='SELECT * FROM user WHERE USERNAME='+QuotedStr(uubah);
      active:=true;
      first;
    end;
    txtUsername.Text:=ZQuery1.fieldbyname('USERNAME').AsString;
    txtPass.Text:=ZQuery1.fieldbyname('PASSWORD').AsString;}
    btnUbah.Enabled:=false;
    btnTambah.Enabled:=false;
    btnHapus.Enabled:=false;
    GroupBox2.Visible:=true;
    txtUsername.Text:='';
    txtPass.Text:='';
    tipe:=0;
  end else
    ShowMessage('Anda tak memiliki hak melakukan perubahan');
end;

procedure TcrudForm.btnUbahClick(Sender: TObject);
var
  i:integer;
begin
  if (cekRole(uname)) and (uubah<>'') then begin
    with ZQuery1 do begin
      active:=false;
      sql.clear;
      sql.text:='SELECT * FROM user WHERE USERNAME='+QuotedStr(uubah);
      active:=true;
      first;
    end;
    txtUsername.Text:=ZQuery1.fieldbyname('USERNAME').AsString;
    txtPass.Text:=ZQuery1.fieldbyname('PASSWORD').AsString;
    if (ZQuery1.fieldbyname('ROLE').AsInteger=0) then
      ComboBox1.ItemIndex:=0
    else
      ComboBox1.ItemIndex:=1;

    //kode
    for i:=1 to ComboBox1.Items.Count-1 do begin
      if (ukode=ComboBox1.Items[i]) then begin
        ComboBox1.ItemIndex:=i;
        nukode:=ukode;
      end;
    end;
    //
    //set kode kec
    
    btnUbah.Enabled:=false;
    btnTambah.Enabled:=false;
    btnHapus.Enabled:=false;
    GroupBox2.Visible:=true;
    tipe:=1;
  end else
    ShowMessage('Anda tak memiliki hak melakukan perubahan');
end;

procedure TcrudForm.btnHapusClick(Sender: TObject);
var
  query:String;
  buttonSelected,i,crow : Integer;
begin
  buttonSelected := MessageDlg('Apakah anda yakin akan menghapus '+uubah+'?',mtConfirmation , mbOKCancel, 0);
  if buttonSelected = mrOK  then begin
    if (cekRole(uname)) and (uubah<>'') then begin
      with ZQuery1 do begin
        active:=false;
        sql.clear;
        sql.text:='DELETE FROM user WHERE USERNAME='+QuotedStr(uubah);
        ExecSQL;
      end;
      ShowMessage('User '+uubah+' berhasil dihapus');
      refresh;
      end else
        ShowMessage('Anda tak memiliki hak melakukan perubahan');
  end;
end;

procedure TCrudForm.refresh;
var
  i,crow:integer;
begin
  for i := 1 to StringGrid1.RowCount - 1
        do StringGrid1.Rows[i].Clear;
  ZTable1.Active:=false;
  ZTable1.TableName:='user';
  if (ukode<>'00') then begin
    ZTable1.Filter:='KODE_KABKOT='+QuotedStr(ukode);
    ZTable1.Filtered:=true;
  end else
    ZTable1.Filtered:=false;
  ZTable1.Active:=true;
  ZTable1.First;
  if (ZTable1.RecordCount>0) then begin
    for crow:=1 to ZTable1.RecordCount do begin
      StringGrid1.RowCount:=crow+1;
      StringGrid1.Cells[0,crow]:=IntToStr(crow);
      StringGrid1.Cells[1,crow]:=ZTable1.fieldbyname('username').AsString;
      if (ZTable1.FieldByName('ROLE').AsString='1') then
        StringGrid1.Cells[4,crow]:='Admin'
      else
        StringGrid1.Cells[4,crow]:='Petugas';
      with zquery1 do begin
        active:=false;
        sql.Clear;
        sql.Text:='SELECT TAMPIL FROM kabkot WHERE KODE='+QuotedStr(ZTable1.fieldbyname('KODE_KABKOT').AsString);
        Active:=true;
        first;
      end;
      StringGrid1.Cells[2,crow]:=zquery1.Fields[0].AsString;
      if(ZTable1.FieldByName('KODE_KEC').AsString<>'000') then
      begin
        with zquery1 do begin
          active:=false;
          sql.Clear;
          sql.Text:='SELECT TAMPIL FROM kecamatan WHERE KODE_KECAMATAN='+QuotedStr(ZTable1.fieldbyname('KODE_KABKOT').AsString+ZTable1.FieldByName('KODE_KEC').AsString);
          Active:=true;
          first;
        end;
        StringGrid1.Cells[3,crow]:=zquery1.Fields[0].AsString;
      end
      else
      begin
        StringGrid1.Cells[3,crow]:='-';
      end;
      ZTable1.Next;
    end;
    for i := 0 to StringGrid1.ColCount - 1 do
      AutoSizeCol(StringGrid1, i);
    end;
end;

function TcrudForm.cekRole(nama:String):Boolean;
var
  tmp:Boolean;
begin
  tmp:=false;
  with zquery1 do begin
    Active:=false;
    sql.Clear;
    sql.Text:='SELECT * FROM user WHERE USERNAME='+QuotedStr(nama);
    Active:=true;
    first;
    if (ZQuery1.FieldByName('ROLE').AsInteger>0) then tmp:=true;
  end;
  Result:=tmp;
end;

function TcrudForm.cekAvail(nama:String) : Boolean;
var
  tmp:Boolean;
begin
  tmp:=true;
  with zquery1 do begin
    Active:=false;
    sql.Clear;
    sql.Text:='SELECT * FROM user WHERE USERNAME='+QuotedStr(nama);
    Active:=true;
    first;
    if (ZQuery1.RecordCount>0) then tmp:=false;
  end;
  Result:=tmp;
end;

procedure TcrudForm.btnTutupClick(Sender: TObject);
begin
  btnUbah.Enabled:=true;
  btnTambah.Enabled:=true;
  btnHapus.Enabled:=true;
  GroupBox2.Visible:=false;
end;

procedure TcrudForm.btnSimpanClick(Sender: TObject);
var
  query,nuname,npass,nurole, kecamatan:String;
  buttonSelected : Integer;
begin
  buttonSelected := MessageDlg('Apakah anda yakin dengan isiannya?',mtConfirmation , mbOKCancel, 0);
  nuname:=txtUsername.Text;
  npass:=txtPass.text;
  nurole:=IntToStr(ComboBox1.ItemIndex);
  if(skec='') then kecamatan:='000'
  else kecamatan:=copy(skec,3,3);
  if (buttonSelected = mrOK) and (nukode<>'') and (nuname<>'') and (npass<>'')  then begin
    if (tipe=0) then begin
      //tambah
      if cekAvail(nuname) then begin
        with zquery1 do begin
          active:=false;
          sql.clear;
          sql.text:='INSERT INTO user (USERNAME,PASSWORD,KODE_KABKOT, KODE_KEC,ROLE) values ('+
          QuotedStr(nuname)+
          ','+QuotedStr(npass)+
          ','+QuotedStr(nukode)+
          ','+QuotedStr(kecamatan)+
          ','+nurole+')';
          ExecSQL;
        end;
        ShowMessage('Anda berhasil menambah user : '+nuname);
      end else
        ShowMessage('User telah terdapat');
    end else begin
      //ubah
      with zquery1 do begin
        active:=false;
        sql.clear;
        sql.text:='UPDATE user set USERNAME='+QuotedStr(nuname)+
        ',PASSWORD='+QuotedStr(npass)+
        ',KODE_KABKOT='+QuotedStr(nukode)+
        ',KODE_KECAMATAN='+QuotedStr(kecamatan)+
        ',ROLE='+nurole+' WHERE USERNAME='+QuotedStr(uubah);
        ExecSQL;
      end;
      ShowMessage('Anda berhasil mengubah user : '+uubah);
    end;
    btnUbah.Enabled:=true;
    btnTambah.Enabled:=true;
    btnHapus.Enabled:=true;
    GroupBox2.Visible:=false;
    refresh;
  end else
    ShowMessage('Cek lagi isiannya');
end;

procedure TcrudForm.ComboBox2Change(Sender: TObject);
var
  pilihan:String;
begin
  if (ComboBox2.ItemIndex > 0) then
  begin
    pilihan := ComboBox2.Items[ComboBox2.ItemIndex];
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kabkot';
        Filter := 'TAMPIL = '+QuotedStr(pilihan);
        Filtered := True;
        Active := True;
        First;
        nukode := FieldByName('KODE').AsString;
      end;
    with zquery1 do begin
          active:=false;
          sql.clear;
          sql.Text:='SELECT * FROM kecamatan where KODE_KABKOT='+quotedstr(nukode);
          Active:=true;
          first;
    end;
    ComboBox3.clear;
    ComboBox3.Items.add('PILIH KECAMATAN');
    while (not zquery1.Eof) do begin
      ComboBox3.Items.Add(ZQuery1.fieldbyname('TAMPIL').AsString);
      zquery1.Next;
    end;
  end
  else if (ComboBox2.ItemIndex = 0) then
    begin
      nukode:='00';
      ComboBox3.Clear;
      ComboBox3.Items.Add('PILIH KECAMATAN');
      ComboBox3.itemindex:=0;
    end;

end;

procedure TcrudForm.rbkotaClick(Sender: TObject);
begin
  ComboBox3.Enabled:=false;
  skec:='';
  combobox3.ItemIndex:=0;
end;

procedure TcrudForm.rbkecClick(Sender: TObject);
begin
  ComboBox3.Enabled:=true;
  skec:='';
  combobox3.ItemIndex:=0;
end;

procedure TcrudForm.ComboBox3Change(Sender: TObject);
var pilihan:string;
begin
  if (ComboBox3.ItemIndex > 0) then
  begin
    pilihan := ComboBox3.Items[ComboBox3.ItemIndex];
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kecamatan';
        Filter := 'TAMPIL = '+QuotedStr(pilihan)+' and KODE_KABKOT='+quotedstr(nukode);
        Filtered := True;
        Active := True;
        First;
        skec := FieldByName('KODE_KECAMATAN').AsString;
      end;
  end
  else
  begin
    skec:='';
  end;
end;

end.
